<?php

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2016/2/27
 * Time: 17:12
 */
class Select_model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }

    /**
     * @param array $params =['type'=>学校类型]
     * @return mixed
     */
    function test( array $params )
    {
        //$this->db->select( '*' )->from( 'auth_group' )->join( 'auth_group_access', 'auth_group_access.uid=auth_group.id' )->where( 'uid', $admin_id)->where( 'status', 1 )->get()->result_array();
        extract( $params );

        return $this->db->select()->from( 'school' )->get()->result_array();
    }

    function getProvinces( array $params )
    {
        return $this->db->select( 'id,area_name' )->from( 'area' )->where( 'parent_id', 0 )->get()->result_array();
    }

    function getSchoolByAreaAndWenli( array $params )
    {
        extract( $params );
        //$sql="select school_id,school_name,school_level,(select area_name from area where area.id=school.province_id ) province_name from school where province_id in (".join(',',$college_place).")";
        $where_str = "";
        $where_str .= "province_id in (" . join( ',', $college_place ) . ") and specialty_name<>''";
        if ( !empty( $wen_li ) ) {
            if ( $wen_li == 2 ) {
                $wen_li = '2,3';
            } elseif ( $wen_li == 1 ) {
                $wen_li = '1,3';
            } elseif ( $wen_li == 3 ) {
                $wen_li = '1,2,3';
            }
            $where_str .= " and irt_university_specialty.type in (" . $wen_li . ")";
        }
        $sql = "SELECT
                    irt_university_specialty.id,
                    school_id,
                    school_name,
                    irt_school.score,
                    school_level,
                    specialty_name,
                    (
                        SELECT
                            area_name
                        FROM
                            area
                        WHERE
                            area.id = school.province_id
                    ) province_name,
                    is_constraints,sign
                FROM
                    school
                    LEFT JOIN irt_school on irt_school.id=school.school_id
                LEFT JOIN irt_university_specialty ON school.school_id = irt_university_specialty.irt_school_id
                LEFT JOIN irt_university_specialty_constraints on irt_university_specialty.id=irt_university_specialty_constraints.university_specialty_id
                WHERE
                    $where_str order BY irt_school.score DESC ";
        $school = $this->db->query( $sql )->result_array();

        return $school;
    }

    function getMajorIdByAwards( array $params )
    {
        extract( $params );
        $where_arr = [ ];
        $where_arr[ ] = "ddic_code in (" . join( ',', $awards_code ) . ")";
        $where_arr[ ] = "province_id in (" . join( ',', $college_place ) . ")";
        //p($college_place);
        if ( !empty( $wen_li ) ) {
            if ( $wen_li == 2 ) {
                $wen_li = '2,3';
            } elseif ( $wen_li == 1 ) {
                $wen_li = '1,3';
            } elseif ( $wen_li == 3 ) {
                $wen_li = '1,2,3';
            }
            $where_arr[]= "irt_university_specialty.type in (" . $wen_li . ")";
        }

        $sql = "select *,specialty_name,irt_school.score
                from
                    irt_university_specialty_constraints
                    left join irt_university_specialty on
irt_university_specialty_constraints.university_specialty_id=irt_university_specialty.id
                    LEFT JOIN irt_school on irt_school.id=irt_university_specialty.irt_school_id
                    left join school on school.school_id=irt_school_id where " . join( ' and ', $where_arr );
        $school = $this->db->query( $sql )->result_array();

        return $school;
    }
    function getNoChongfenAndBiyao( array $params )
    {
        extract( $params );
        $where_arr = [ ];
        if(!empty($awards_code)){
            $where_arr[ ] = "ddic_code not in (" . join( ',', $awards_code ) . ")";
        }
        $where_arr[ ] = "province_id in (" . join( ',', $college_place ) . ")";
        $sql = "select university_specialty_id id,irt_school.score
                from
                    irt_university_specialty_constraints
                     left JOIN irt_university_specialty on irt_university_specialty.id=irt_university_specialty_constraints.university_specialty_id
                     LEFT JOIN irt_school on irt_school.id=irt_university_specialty.irt_school_id
                     LEFT JOIN school on school_id=irt_school.id
                     where " . join( ' and ', $where_arr )." group by university_specialty_id";
        $school_tiaojian = $this->db->query( $sql )->result_array();
        $sql="SELECT
	            irt_university_specialty.id id,
	            irt_school.score
              FROM
	              irt_university_specialty
                  LEFT JOIN irt_school ON irt_school.id = irt_university_specialty.irt_school_id
                  LEFT JOIN school ON school_id = irt_school.id where province_id in (" . join( ',', $college_place ) . ") and is_constraints=0";
        $school_notiaojian = $this->db->query( $sql )->result_array();
        $school=array_merge($school_tiaojian,$school_notiaojian);
        return $school;
    }
 
    function getSchoolLessScore( array $params )
    {
        extract( $params );
        $sql = "select * from irt_school where score<$score";
        $school = $this->db->query( $sql )->result_array();

        return $school;
    }

    function getBaodiSchool( array $college_place, $score, $awards_code = [ ], $wen_li = '' )
    {
        //$school_major_list = $this->getSchoolByAreaAndWenli( [ 'college_place' => $college_place, 'wen_li' => $wen_li ] );
        /**
         * 保底：
         *
         * 2.必要条件限制的并且分数比学生少两分的专业
         * 3.分数比学生分数低两分的没有充分必要条件限制的专业
         */
        $baodi_score = $score - 2;
        $major_list_ids=[];
        if(!empty($awards_code)){
            $awards_major = $this->getMajorIdByAwards( [ 'awards_code' => $awards_code,'wen_li'=>$wen_li,'college_place'=>$college_place]);
            foreach($awards_major as $k=>$v){
                if($v['sign']==1&&$v[ 'score' ] <= $baodi_score){
                    $major_list_ids[ "'" . $v[ 'university_specialty_id' ] . "'" ] = $v[ 'university_specialty_id' ];
                }
            }
        }
        $no_chongfen_biyao_school=$this->getNoChongfenAndBiyao([ 'awards_code' => $awards_code,'college_place'=>$college_place]);
        foreach($no_chongfen_biyao_school as $k=>$v)
        {
            if ( $v[ 'score' ] <= $baodi_score  ){
                $major_list_ids[ "'" . $v[ 'id' ] . "'" ] = $v[ 'id' ];
            }
        }
        return $this->formatReturnData( $major_list_ids, $awards_code);
    }

    function getTuijianSchool( array $college_place, $score, $awards_code = [ ], $wen_li = '' )
    {
        //$school_major_list = $this->getSchoolByAreaAndWenli( [ 'college_place' => $college_place, 'wen_li' => $wen_li ] );
        /**
         * 推荐：
         * 1.必要条件限制的专业并且比学生得分高1分到低一分的专业
         * 2.满足比学生得分低一分的但比学生分数高一分的这个区间内的学校并且没有充分必要条件限制的专业
         * 3.充分的专业
         */
        $tuijian_min_score = $score - 1;
        $tuijian_max_score = $score + 1;
        $major_list_ids=[];
        if(!empty($awards_code)){
            $awards_major = $this->getMajorIdByAwards( [ 'awards_code' => $awards_code,'wen_li'=>$wen_li,'college_place'=>$college_place]);
            foreach($awards_major as $k=>$v){
                if($v['sign']==1&&$v[ 'score' ] <= $tuijian_max_score && $v[ 'score' ] >= $tuijian_min_score ){
                        $major_list_ids[ "'" . $v[ 'university_specialty_id' ] . "'" ] = $v[ 'university_specialty_id' ];
                }elseif($v['sign']==0){
                    $major_list_ids[ "'" . $v[ 'university_specialty_id' ] . "'" ] = $v[ 'university_specialty_id' ];
                }
            }
        }
        $no_chongfen_biyao_school=$this->getNoChongfenAndBiyao([ 'awards_code' => $awards_code,'college_place'=>$college_place]);
        foreach($no_chongfen_biyao_school as $k=>$v)
        {
            if ( $v[ 'score' ] <= $tuijian_max_score && $v[ 'score' ] >= $tuijian_min_score ){
                //v($v);
                $major_list_ids[ "'" . $v[ 'id' ] . "'" ] = $v[ 'id' ];
            }
        }
        return $this->formatReturnData( $major_list_ids,$awards_code );

    }

    function getChongciSchool( array $college_place, $score, $awards_code = [ ], $wen_li = '' )
    {
        //$school_major_list = $this->getSchoolByAreaAndWenli( [ 'college_place' => $college_place, 'wen_li' => $wen_li ] );
        /**
         * 冲刺：
         * 1.必要条件限制的专业并且比学生得分高两分到三分的专业
         * 2.满足比学生得分高两分到三分学校并且没有充分必要条件限制的专业
         */
        $major_list_ids=[];
        $chongci_min_score = $score + 2;
        $chongci_max_score = $score + 3;
        if(!empty($awards_code)){
            $awards_major = $this->getMajorIdByAwards( [ 'awards_code' => $awards_code,'wen_li'=>$wen_li,'college_place'=>$college_place]);
            foreach($awards_major as $k=>$v){
                if($v['sign']==1){
                    if ( $v[ 'score' ] <= $chongci_max_score && $v[ 'score' ] >= $chongci_min_score ){
                        $major_list_ids[ "'" . $v[ 'university_specialty_id' ] . "'" ] = $v[ 'university_specialty_id' ];
                    }
                }
            }
        }
        $no_chongfen_biyao_school=$this->getNoChongfenAndBiyao([ 'awards_code' => $awards_code,'college_place'=>$college_place]);
        foreach($no_chongfen_biyao_school as $k=>$v)
        {
            if ( $v[ 'score' ] <= $chongci_max_score && $v[ 'score' ] >= $chongci_min_score ){
                $major_list_ids[ "'" . $v[ 'id' ] . "'" ] = $v[ 'id' ];
            }
        }
        return $this->formatReturnData( $major_list_ids,$awards_code );
    }

    function formatReturnData( array $params,array $awards_code )
    {
        $tmp_key_arr = [ ];
        if(empty($params)){
            return [];
        }
        $sql = "select
                    irt_university_specialty.id,
                    school_id,
                    school_name,
                    school_level,
                    specialty_name,
                    area_name province_name
                from
                    irt_university_specialty
                    left join school on irt_school_id=school_id
                    left join area on area.id=province_id
                    where irt_university_specialty.id in (" . join( ',', $params ).")";
        $school = $this->db->query( $sql )->result_array();
        //p($school);
        //v($school);
        $where_arr[]="sign=0";
        if(!empty($awards_code)){
            $sql="
                select sign,university_specialty_id
                from irt_university_specialty_constraints
                where ".join(' and ',$where_arr);
            $chongfengs = $this->db->query( $sql )->result_array();
            foreach($school as $k=>&$v){
                foreach($chongfengs as $p=>$q){
                    if($v['id']==$q['university_specialty_id']){
                        $v['sign']=0;
                    }
                }
            }
        }

        $options = $school;
        foreach ( $options as $k => &$v ) {
            $tmp_key = "";
            foreach ( $v as $p => &$q ) {
                if ( $p == "school_id" ) $tmp_key = $q;
                if ( !isset( $tmp_key_arr[ $tmp_key ][ 'major_list' ] ) ) {
                    $tmp_key_arr[ $tmp_key ][ 'major_list' ] = "";
                    //continue;
                }
                if ( $p == 'specialty_name' ) {
                    if(isset($v['sign'])&&$v['sign']==0){
                        $tmp_key_arr[ $tmp_key ][ 'major_list' ] = $tmp_key_arr[ $tmp_key ][ 'major_list' ] . "*".$q .",";
                    }else{
                        $tmp_key_arr[ $tmp_key ][ 'major_list' ] = $tmp_key_arr[ $tmp_key ][ 'major_list' ] . $q . ",";
                    }

                }
                $tmp_key_arr[ $tmp_key ][ $p ] = $q;
            }
        }
        array_shift($tmp_key_arr);
        //p($tmp_key_arr);
        $tmp_key_arr=array_values($tmp_key_arr);
        return $tmp_key_arr;
    }
<<<<<<< HEAD
    public function getData(){
        $sql="select * from blog_admin_log";
        $school=$this->db->query($sql)->result_array();
        return $school;
    }
    /*function formatReturnData(array $params)
    {
        $tmp_major_list=[];
        foreach($params as $k=>&$v){
            if(empty($tmp_major_list)){
                $v['major_list']=$v['specialty_name'];
                $tmp_major_list[]=$v;
                continue;
            }
            foreach($tmp_major_list as $p=>&$q)
            {
                if($v['school_id']==$q['school_id']){
                    $q['major_list'].=$v['specialty_name'].",";
                }
            }
        }
        return $tmp_major_list;
    }*/
=======
>>>>>>> 96e92588ab6f422bb806115b2685e1c7330dc0e0
}